|
Installation note - SQL Database Scripts | |
To conduct the Advanced Java Course, the following
databases need to be installed:
| |
| Note: You should back up the existing databases so that they can be restored later. | |
| A brief description of the various scripts and their content is given below. | |
| Scripts | Description |
| CreateServerLogins.bat | This script creates 30 SQL Server logins for students. Running this script is a one-time activity. |
| InstallEBDB.bat | This script creates the EarnestBank database and its objects. You must back up the database after creating it. |
| InstallFMWDB.bat | This script creates the FlyMyWay database and its objects. You must back up the database after creating it. |
Frequently asked questions about scripts:
Q) Which script needs to be executed first?
Answer) You need to execute the CREATESQLSERVERLOGINS.BAT before executing any other script. The contents of the script are:
osql -Usa -P -S%COMPUTERNAME% -iCreateSQLServerLogins.sql
You need to modify the script to be able to execute it on your machine. For example, if the sa login has password as "password", you need to change the command to:
osql -Usa -Ppassword -S%COMPUTERNAME% -iCreateSQLServerLogins.sql
Save the changes and execute the batch file. This batch-file would call the CreateSQLServerLogins script, which in turn would use the sp_addlogin username, password command to add user logins to SQL Server. It would create 30 users with the names user1, user2, user3 ... user30. It would also create a faculty login. You should ask students to use one of these logins to connect to the SQL Server. Do not let them use the faculty or the sa login.
This script needs to be executed only after the installation of the SQL server since these logins are used to connect to the SQL Server.
Q) How should I create the EarnestBank database?
Answer) To create the EarnestBank database, you can use the following scripts:
INSTALLEBDB.BAT. Its contents are:
|
osql -Usa -P -S%COMPUTERNAME% -iCreateEarnestBankDB.sql |
You need to modify the script to be able to execute the script. For example, if the sa login is "password", you need to modify the script to:
|
osql -Usa -Ppassword -S%COMPUTERNAME% -iCreateEarnestBankDB.sql |
Q) What is the purpose and sequence of executing these scripts?
Answer) You should execute these scripts in the following sequence:
1) CREATESQLSERVERLOGINS.BAT
2) INSTALLEBDB.BAT
3) INSTALLFlyMyWay.BAT
Q) Why should I execute the batch file and not directly execute the sql scripts?
Answer) The batch file uses a for loop to create 30 copies of the same tables. If you execute the script by loading it in the Query analyzer, you would just get one copy of the tables. Thirty copies are required as all the users should have there own copy of the tables. In case you don't create 30 copies, if one person drops a table, the other person would not be able to use that table.
Q) How do I execute the .BAT files?
Answer) You can double click the batch files execute them. You can also access the command prompt, and type the name of a batch file on the command prompt, and then press Enter to execute it.
Q) What is present in the SQL files?
Answer) The SQL files contain SQL statements to create the EarnestBank and the FlyMyWay database.
Q) On which machine should you execute these scripts?
Answer) You should execute these scripts on the machine where the SQL Server is installed.
Q) Do I have to execute the script on all the machines?
Answer) No, you just need to install the script on one machine and other persons can use the Query Analyzer to connect to the SQL server and use the database. You need to install SQL Server on one machine and the Client components on rest of the machines. Only in case you have problems in networking and are not able to connect to the SQL server and the machine configuration is such that you can install SQL Server on all the machines, should you execute the script on all the machines.
Q) How should I back up and restore the database?
Answer) To back up the database:
1) Invoke Enterprise Manager.
2) Right-Click the database to be backed up.
3) Select All Tasks.
4) Select the backup option.
The following figure would be displayed:
5) Click the Add button.
6) Enter the name of the backup file.
7) Click OK to start the backup.
To restore the data from the backup:
1) Delete the existing database.
2) Create a database with the same name that existed on the server previously.
3) Right-Click the database to be restored.
4) Select All Tasks.
5) Select the restore database option.
Following dialog box would be displayed:
6) Click the From device option.
7) Click the Select Devices button to open the Choose Restore Device dialog box.
8) In the dialog box, select the name of the file that you used while creating the backup.
9) Click OK to return to the Restore database dialog box.
10) Click OK to start restoring the database.
Q) How do I change the password of sa or faculty login?
Answer) You can change the password of any user using the sp_password command. The syntax of the command is:
sp_password old_password, new_password, login_name
Example
sp_password null,'newpassword','faculty'
Q) If one user say, user1, drops a table, say Account_Holder, then can another user, say user2, drop the same table?
Answer) Yes, if you execute the batch files in the proper sequence. This is possible as each table is referenced by servername.databasename.username.tablename. For Example, if you have a table called Account_Holder in the EarnestBank database on the SQL Server named Serv1 created by user1, you can reference it as Serv1.EarnestBank.user1.Account_Holder. When you execute the batch file, 30 copies of the schema are created. Each user has his/her own copy.